﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using KZ.Exception;

namespace Process.DAL
{
    public class DataAccessHelper
    {
        #region Private Variables

        private static string _ConnStr;
        private static SqlConnection _SqlConnection;
        private static SqlTransaction _SqlTransaction;
        private static SqlCommand _SqlCmd;

        #endregion Private Variables

        #region Private Methods

        private static void OpenConnection()
        {
            if (_SqlConnection.State == ConnectionState.Closed)
            {
                _SqlConnection.Open();
            }
        }

        private static void CloseConnection()
        {
            if (_SqlConnection.State == ConnectionState.Open)
            {
                _SqlConnection.Close();
            }
        }

        #endregion

        #region Constructors

        static DataAccessHelper()
        {
            if (String.IsNullOrEmpty(_ConnStr))
            {
                _ConnStr = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
                _SqlConnection = new SqlConnection(_ConnStr);
                _SqlCmd = new SqlCommand { Connection = _SqlConnection, CommandType = CommandType.StoredProcedure };
            }
        }

        #endregion Constructors

        #region Public Methods

        public static void ClearCommand()
        {
            if (_SqlCmd != null)
            {
                _SqlCmd.Parameters.Clear();
                _SqlCmd.CommandText = String.Empty;
            }
        }

        public static void AddParameter(string parameterName, SqlDbType type, object value)
        {
            _SqlCmd.Parameters.Add(parameterName, type).Value = value;
        }

        public static void AddParameter(string parameterName, SqlDbType type, object value, ParameterDirection direction)
        {
            _SqlCmd.Parameters.Add(parameterName, type).Value = value;
            _SqlCmd.Parameters[parameterName].Direction = direction;
        }

        public static object GetParameterValue(string parameterName)
        {
            return _SqlCmd.Parameters[parameterName].Value;
        }

        public static void BeginTransaction(IsolationLevel iso)
        {
            try
            {
                if (_SqlConnection.State == ConnectionState.Open)
                {
                    _SqlTransaction = _SqlConnection.BeginTransaction(iso);
                }
                else
                {
                    _SqlConnection.Open();
                    _SqlTransaction = _SqlConnection.BeginTransaction(iso);
                }
            }
            catch (Exception)
            {
                throw new DataAccessException("Có lỗi trong quá trình xử lý");
            }
        }

        public static void CommitTransaction()
        {
            try
            {
                if (_SqlConnection.State == ConnectionState.Open)
                {
                    _SqlTransaction.Commit();
                }
                else
                {
                    throw new Exception();
                }
            }
            catch (Exception)
            {
                throw new DataAccessException("Có lỗi trong quá trình xử lý");
            }
            finally
            {
                _SqlTransaction.Dispose();
                _SqlConnection.Close();
            }
        }

        public static void RollbackTransaction()
        {
            try
            {
                if (_SqlConnection.State == ConnectionState.Open)
                {
                    _SqlTransaction.Rollback();
                }
                else
                {
                    throw new Exception();
                }
            }
            catch (Exception)
            {
                throw new DataAccessException("Có lỗi trong quá trình xử lý");
            }
            finally
            {
                _SqlTransaction.Dispose();
                _SqlConnection.Close();
            }
        }

        public static int ExecuteNonQuery(string cmdText)
        {
            OpenConnection();
            _SqlCmd.CommandText = cmdText;
            return _SqlCmd.ExecuteNonQuery();
        }

        public static int ExecuteNonQueryWithTransaction(string cmdText)
        {
            _SqlCmd.CommandText = cmdText;
            _SqlCmd.Transaction = _SqlTransaction;
            return _SqlCmd.ExecuteNonQuery();
        }

        public static IDataReader ExecuteQuery(string cmdText)
        {
            OpenConnection();
            _SqlCmd.CommandText = cmdText;
            return _SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public static IDataReader ExecuteQueryWithTransaction(string cmdText)
        {
            _SqlCmd.CommandText = cmdText;
            _SqlCmd.Transaction = _SqlTransaction;
            return _SqlCmd.ExecuteReader();
        }

        public static DataTable ExecuteQueryDataTable(string cmdText)
        {
            try
            {
                _SqlCmd.CommandText = cmdText;
                SqlDataAdapter adapter = new SqlDataAdapter(_SqlCmd);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return dt;
            }
            finally
            {
                CloseConnection();
            }
        }

        public static DataTable ExecuteQueryDataTableWithTransaction(string cmdText)
        {
            try
            {
                _SqlCmd.CommandText = cmdText;
                _SqlCmd.Transaction = _SqlTransaction;
                SqlDataAdapter adapter = new SqlDataAdapter(_SqlCmd);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return dt;
            }
            finally
            {
                CloseConnection();
            }
        }

        #endregion Public Methods
    }
}